﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace ShoppingApp
{
    public partial class _Default : System.Web.UI.Page
    {
        private string myStr = "string2";

        protected void Page_Load(object sender, EventArgs e)
        {
            //Application["AppStarts"] = (int)Application["AppStarts"] + 1;
            //AppStartsLabel.Text = Application["AppStarts"].ToString();

            if (IsPostBack)
            {
                string str = (string)ViewState["strSql"];
                System.Diagnostics.Debug.WriteLine(str);
            }
        }

        protected void Page_PreRender(object sender, EventArgs e)
        {
            string strSql = "string";
            Session["strSql"] = strSql;
            
            ViewState.Add("strSql", myStr);
        }

        /// <summary>
        /// Builds connection string in order to connect to ShoppingAppDb for specified user
        /// </summary>
        /// <returns>Connection string</returns>
        private string BuildConnectionString()
        {
            string result = null;

            SqlConnectionStringBuilder strConn = new SqlConnectionStringBuilder();
            strConn.DataSource = @".\SQLEXPRESS";
            strConn.InitialCatalog = "ShoppingAppDb";
            strConn.UserID = "AspNetUser";
            strConn.Password = "password";
            result = strConn.ConnectionString;

            return result;
        }

        protected void GetLoginButton_Click(object sender, EventArgs e)
        {
            string strCon, strSql;
            strCon = BuildConnectionString();

            //Create new SqlConnection object using given string
            using (SqlConnection cn = new SqlConnection(strCon))
            {
                strSql = "SELECT Id, FirstName, LastName, LoginName FROM dbo.SimpleLogins WHERE Id=1";
                //strSql = "DELETE FROM dbo.SimpleLogins WHERE Id=100";
                
                //Open the connection
                cn.Open();
                
                //Create SqlCommand object in order to query database
                using (SqlCommand cmd = new SqlCommand(strSql, cn))
                {
                    try
                    {
                        //Use SqlDataReader to retrieve sequentially results
                        using(SqlDataReader reader = cmd.ExecuteReader())
                        {
                        //int zmienna = cmd.ExecuteNonQuery();
                        //System.Diagnostics.Debug.WriteLine("zmienna wynosi: ", zmienna.ToString());

                            while (reader.Read())
                            {
                                //Use column names to retrieve value
                                System.Diagnostics.Debug.WriteLine("Id: " + reader["Id"].ToString());
                                System.Diagnostics.Debug.WriteLine("FirstName: " + reader["FirstName"].ToString());
                                System.Diagnostics.Debug.WriteLine("LastName: " + reader["LastName"].ToString());
                                System.Diagnostics.Debug.WriteLine("LoginName: " + reader["LoginName"].ToString());
                            }
                            reader.Close();
                        }
                    }
                    catch (Exception ex)
                    {
                        //implement
                                                
                    }
                }
            }
        }

        protected void GetTypesButton_Click(object sender, EventArgs e)
        {
            string strCon, strSql;
            byte idValue = 0;
            int ageValue = 0;
            decimal experienceValue = 0;

            strCon = BuildConnectionString();
            
            //Create new SqlConnection object using given string
            using (SqlConnection cn = new SqlConnection(strCon))
            {
                strSql = "SELECT Id, Age, ExperienceLevel FROM dbo.SimpleLogins WHERE Id = 1";
                  
                //Open the connection
                cn.Open();
                
                //Create SqlCommand object in order to query database
                using (SqlCommand cmd = new SqlCommand(strSql, cn))
                {
                    try
                    {
                        //Use SqlDataReader to retrieve sequentially results
                        using(SqlDataReader reader = cmd.ExecuteReader())
                        {
                            if (reader.FieldCount == 3)
                            {
                                while (reader.Read())
                                {
                                    //ageValue = reader["Age"];
                                    //experienceValue = reader["ExperienceLeveL"];

                                    //Use ordinal numbers to retrieve results
                                    idValue = reader.GetByte(0);
                                    ageValue = reader.GetInt32(1);
                                    experienceValue = reader.GetDecimal(2);
                                }
                            }
                            reader.Close();
                        }
                    }
                    catch (Exception ex)
                    {
                        //implement
                    }
                }
            }

            //Write these values to output 
            System.Diagnostics.Debug.WriteLine("idValue: " + idValue.ToString());
            System.Diagnostics.Debug.WriteLine("ageValue: " + ageValue.ToString());
            System.Diagnostics.Debug.WriteLine("experienceValue: " + experienceValue.ToString());

        }

        protected void SomeNullValuesButton_Click(object sender, EventArgs e)
        {
            string strCon, strSql;
            byte idValue = 0;
            int? ageValue = 0;
            decimal? experienceValue = 0;
            
            strCon = BuildConnectionString();

            //Create new SqlConnection object using given string
            using (SqlConnection cn = new SqlConnection(strCon))
            {
                strSql = "SELECT Id, Age, ExperienceLevel FROM dbo.SimpleLogins WHERE Id = 3";

                //Open the connection
                cn.Open();

                //Create SqlCommand object in order to query database
                using (SqlCommand cmd = new SqlCommand(strSql, cn))
                {
                    try
                    {
                        //Use SqlDataReader to retrieve sequentially results
                        using (SqlDataReader reader = cmd.ExecuteReader())
                        {
                            try
                            {
                                if (reader.FieldCount == 3)
                                {
                                    while (reader.Read())
                                    {
                                        //Use ordinal numbers to retrieve results
                                        idValue = reader.GetByte(0);
                                        
                                        
                                        if (reader.IsDBNull(1))
                                        {
                                            ageValue = null;
                                        }
                                        else
                                        {
                                            ageValue = reader.GetInt32(1);
                                        }

                                        if (reader.IsDBNull(2))
                                        {
                                            experienceValue = null;
                                        }
                                        else
                                        {
                                            experienceValue = reader.GetDecimal(2);
                                        }

                                        //ageValue = reader.GetInt32(1);
                                        //experienceValue = reader.GetDecimal(2);
                                    }
                                }
                            }
                            catch (SqlNullValueException ex)
                            {
                                System.Diagnostics.Debug.WriteLine(ex.Message);
                            }
                            finally
                            {
                                reader.Close();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        //implement
                    }
                }
            }

            //Write these values to output 
            System.Diagnostics.Debug.WriteLine("idValue: " + idValue.ToString());
            System.Diagnostics.Debug.WriteLine("ageValue: " + ageValue.ToString());
            System.Diagnostics.Debug.WriteLine("experienceValue: " + experienceValue.ToString());
        }

        protected void GetSqlTypesButton_Click(object sender, EventArgs e)
        {
            string strCon, strSql;
            SqlByte idValue = 0;
            SqlInt32 ageValue = 0;
            SqlDecimal experienceValue = 0;
            
            strCon = BuildConnectionString();

            //Create new SqlConnection object using given string
            using (SqlConnection cn = new SqlConnection(strCon))
            {
                strSql = "SELECT Id, Age, ExperienceLevel FROM dbo.SimpleLogins WHERE Id = 3";

                //Open the connection
                cn.Open();

                //Create SqlCommand object in order to query database
                using (SqlCommand cmd = new SqlCommand(strSql, cn))
                {
                    try
                    {
                        //Use SqlDataReader to retrieve sequentially results
                        using (SqlDataReader reader = cmd.ExecuteReader())
                        {
                            try
                            {
                                if (reader.FieldCount == 3)
                                {
                                    while (reader.Read())
                                    {
                                        //Use ordinal numbers to retrieve results
                                        idValue = reader.GetSqlByte(0);

                                        /*
                                        if (reader.IsDBNull(1))
                                        {
                                            ageValue = null;
                                        }
                                        else
                                        {
                                            ageValue = reader.GetInt32(1);
                                        }*/

                                        ageValue = reader.GetSqlInt32(1);
                                        experienceValue = reader.GetSqlDecimal(2);
                                    }
                                }
                            }
                            catch (SqlNullValueException ex)
                            {
                                System.Diagnostics.Debug.WriteLine(ex.Message);
                            }
                            finally
                            {
                                reader.Close();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        //implement
                    }
                }
            }

            //Write these values to output 
            System.Diagnostics.Debug.WriteLine("idValue: " + idValue.ToString());
            System.Diagnostics.Debug.WriteLine("ageValue: " + ageValue.ToString());
            System.Diagnostics.Debug.WriteLine("experienceValue: " + experienceValue.ToString());
        }

        protected void insertDataIntoTableButton_Click(object sender, EventArgs e)
        {
            //Select all logins from table
            string strSql = "SELECT LoginName FROM SimpleLogins";
            List<string> loginsList = new List<string>(2);//initial capacity set to 2
            int ordinalColumn = -10;//bad column number at start

            using (SqlConnection cn = new SqlConnection(BuildConnectionString()))
            {
                try
                {
                    cn.Open();

                    using (SqlCommand cmd = new SqlCommand(strSql, cn))
                    {
                        try
                        {   
                            using (SqlDataReader reader = cmd.ExecuteReader())
                            {
                                try
                                {
                                    ordinalColumn = reader.GetOrdinal("LoginName");

                                    while (reader.Read())
                                    {
                                        loginsList.Add(reader.GetString(ordinalColumn));
                                    }
                                }
                                catch (Exception ex)
                                {
                                    //reader exception implement
                                }
                                finally
                                {
                                    loginsList.TrimExcess();
                                    reader.Close();
                                }

                            }

                        }
                        catch (Exception ex)
                        {
                            //implement
                        }

                    }
                }
                catch (Exception ex)
                {
                    //implement
                }

            }

            //Connection closed - data in List named loginsList
            populateCheckBoxList(loginsList);
        }

        /// <summary>
        /// Populates CheckBoxList with options to select
        /// </summary>
        private void populateCheckBoxList(List<string> cbl)
        {
            try
            {
                //Bind data from loginsList to CheckBoxList1
                this.CheckBoxList1.DataSource = cbl;
                this.CheckBoxList1.DataBind();
            }
            catch (Exception ex)
            {
                //implement
            }
        }

        /// <summary>
        /// Works like MessageBox in WindowsForms
        /// </summary>
        /// <param name="msg">Message shown to user</param>
        private void showMessage(string msg)
        {
            Label lbl = new Label();
            lbl.Text = "<script language='javascript'>" + Environment.NewLine +
                "window.alert('" + msg + "')</script>";
            Page.Controls.Add(lbl);
        }

        protected void SubmitButton_Click(object sender, EventArgs e)
        {
            string message = null;
            message += "You have chosen following logins: ";

            
            if (CheckBoxList1.Items != null)
            {
                foreach (ListItem elem in CheckBoxList1.Items)
                {
                    if (elem.Selected)
                    {
                        message += elem.Text + "; ";
                    }
                }
            }

            showMessage(message);
        }

        protected void SubmitSelectionButton_Click(object sender, EventArgs e)
        {
            List<string> myList = new List<string>(2);
            
            if (CheckBoxList1.Items != null)
            {
                foreach (ListItem elem in CheckBoxList1.Items)
                {
                    if (elem.Selected)
                    {
                        myList.Add(elem.Value);
                    }
                }
            }

            Session["myList"] = myList;
            Server.Transfer("Details.aspx");
        }

        protected void ExperienceButton_Click(object sender, EventArgs e)
        {
            string strSql = "SELECT AVG(ExperienceLevel) FROM dbo.SimpleLogins";

            using (SqlConnection cn = new SqlConnection(BuildConnectionString()))
            {
                try
                {
                    cn.Open();

                    using (SqlCommand cmd = new SqlCommand(strSql, cn))
                    {
                        try
                        {

                            //using (SqlDataReader reader = cmd.ExecuteReader())
                            //{
                            //    //Take first row from reader
                            //    reader.Read();

                            //    //Assign result to ExperiencaLabel
                            //    ExperienceLabel.Text = reader[0].ToString();
                            //}

                            ExperienceLabel.Text = cmd.ExecuteScalar().ToString();
                        }
                        catch (SqlException ex)
                        {
                            System.Diagnostics.Debug.WriteLine(ex.Message);
                        }

                    }

                }
                catch (Exception ex)
                {
                    //implement
                }


            }
        }

    }
}
